﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using QLVatTu.Class;
using QLVatTu.InPhieu;
using System.Globalization;

namespace QLVatTu.NghiepVu
{
    public partial class frmQuanLyPhieuNhapMua : Form
    {
        public frmQuanLyPhieuNhapMua()
        {
            InitializeComponent();
        }

        KetNoiDB cn = new KetNoiDB();

        private void btnThem_Click(object sender, EventArgs e)
        {
            frmPhieuNhapMua frm = new frmPhieuNhapMua();
            frm.ShowDialog();
            load_dtgPhieuNhap();
        }

        private void btnThoat_Click(object sender, EventArgs e)
        {
            if (this.TopLevel == false) this.Parent.Parent.Controls.Remove(this.Parent);
            else this.Close();
        }

        private void load_dtgPhieuNhap(string dieuKien = null)
        {
            string sql = "";
            if (dieuKien != null)
            {
                sql = @"
                    SELECT       row_number() over(order by tbl_PhieuNhapKho.SoPhieu) as STT, dbo.tbl_PhieuNhapKho.SoPhieu, dbo.tbl_PhieuNhapKho.NgayNhap, dbo.tbl_DoiTac.TenDoiTac, dbo.tbl_Kho.TenKho, dbo.tbl_CongTrinhDuAn.TenCongTrinhDuAn, 
                                          dbo.tbl_PhieuNhapKho.NguoiNhan, dbo.tbl_PhieuNhapKho.DienGiai, tbl_PhieuNhapKho.MaPhieuYeuCau, XacNhan = case XacNhan when '0' then N'Chưa xác nhận' when '1' then N'Đã xác nhận' end
                    FROM         dbo.tbl_PhieuNhapKho INNER JOIN
                                          dbo.tbl_DoiTac ON dbo.tbl_PhieuNhapKho.MaDoiTac = dbo.tbl_DoiTac.MaDoiTac INNER JOIN
                                          dbo.tbl_Kho ON dbo.tbl_PhieuNhapKho.MaKho = dbo.tbl_Kho.MaKho INNER JOIN
                                          dbo.tbl_CongTrinhDuAn ON dbo.tbl_PhieuNhapKho.MaCongTrinhDuAn = dbo.tbl_CongTrinhDuAn.MaCongTrinhDuAn
                    "
                    + dieuKien;
            }
            else
            {
                sql = @"
                   SELECT    row_number() over(order by tbl_PhieuNhapKho.SoPhieu) as STT, dbo.tbl_PhieuNhapKho.SoPhieu, dbo.tbl_PhieuNhapKho.NgayNhap, dbo.tbl_DoiTac.TenDoiTac, dbo.tbl_Kho.TenKho, dbo.tbl_CongTrinhDuAn.TenCongTrinhDuAn, 
                                      dbo.tbl_PhieuNhapKho.NguoiNhan, dbo.tbl_PhieuNhapKho.DienGiai, tbl_PhieuNhapKho.MaPhieuYeuCau, XacNhan = case XacNhan when '0' then N'Chưa xác nhận' when '1' then N'Đã xác nhận' end
                   FROM         dbo.tbl_PhieuNhapKho INNER JOIN
                                          dbo.tbl_DoiTac ON dbo.tbl_PhieuNhapKho.MaDoiTac = dbo.tbl_DoiTac.MaDoiTac INNER JOIN
                                          dbo.tbl_Kho ON dbo.tbl_PhieuNhapKho.MaKho = dbo.tbl_Kho.MaKho INNER JOIN
                                          dbo.tbl_CongTrinhDuAn ON dbo.tbl_PhieuNhapKho.MaCongTrinhDuAn = dbo.tbl_CongTrinhDuAn.MaCongTrinhDuAn
                    WhERE tbl_PhieuNhapKho.MaLoaiPhieu='LP001'
                ";
            }
            dtgPhieuNhap.DataSource=cn.getTable(sql);
            
        }

        private void loadData()
        {
            cmbKhoNhap.DataSource = cn.getTable("SELECT [MaKho],[TenKho] FROM [tbl_Kho]");
            cmbKhoNhap.DisplayMember = "TenKho";
            cmbKhoNhap.ValueMember = "MaKho";

            cmbNhaCungCap.DataSource = cn.getTable("SELECT [MaDoiTac],[TenDoiTac] FROM [tbl_DoiTac] WHERE [NhaCungCap] = 1");
            cmbNhaCungCap.DisplayMember = "TenDoiTac";
            cmbNhaCungCap.ValueMember = "MaDoiTac";

            cmbCongTrinhDuAn.DataSource = cn.getTable("SELECT [MaCongTrinhDuAn],[TenCongTrinhDuAn] FROM [tbl_CongTrinhDuAn]");
            cmbCongTrinhDuAn.DisplayMember = "TenCongTrinhDuAn";
            cmbCongTrinhDuAn.ValueMember = "MaCongTrinhDuAn";

            load_dtgPhieuNhap();
        }

        private void frmQuanLyPhieuNhapMua_Load(object sender, EventArgs e)
        {
            if (cn.KetNoi())
            {
                loadData();
            }
            else
            {
                MessageBox.Show("Kết nối CSDL thất bại!");
                this.Close();
            }
        }

        private void dtgPhieuNhap_SelectionChanged(object sender, EventArgs e)
        {
            if (dtgPhieuNhap.SelectedRows.Count > 0 && dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value!=null)
            {
                string sql = @"SELECT  row_number() over(order by tbl_ChiTietPhieuNhap.SoPhieu) as STT, tbl_ChiTietPhieuNhap.MaVatTu, dbo.tbl_VatTu.TenVatTu, dbo.tbl_DonViTinh.TenDonViTinh, dbo.tbl_ChiTietPhieuNhap.SoLuong
                               FROM         dbo.tbl_ChiTietPhieuNhap INNER JOIN
                                                      dbo.tbl_DonViTinh ON dbo.tbl_ChiTietPhieuNhap.MaDonViTinh = dbo.tbl_DonViTinh.MaDonViTinh INNER JOIN
                                                      dbo.tbl_VatTu ON dbo.tbl_ChiTietPhieuNhap.MaVatTu = dbo.tbl_VatTu.MaVatTu
                               WHERE     dbo.tbl_ChiTietPhieuNhap.SoPhieu = '"+dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString()+"'";
                DataTable tbl = cn.getTable(sql);
                dtgChiTietPhieuNhapMua.DataSource = tbl;

                sql = "SELECT [XacNhan] FROM [tbl_PhieuNhapKho] WHERE [SoPhieu] = '" + dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString() + "'";
                bool _XacNhan = bool.Parse(cn.LayDuLieu(sql));
                btnXacNhan.Enabled = !_XacNhan;
            }
        }

        private void btnSua_Click(object sender, EventArgs e)
        {
            if (dtgPhieuNhap.SelectedRows.Count > 0 && dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value != null)
            {
                string sql = "SELECT [XacNhan] FROM [tbl_PhieuNhapKho] WHERE [SoPhieu] = '" + dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString() + "'";
                bool _XacNhan = bool.Parse(cn.LayDuLieu(sql));
                if (_XacNhan == true)
                {
                    MessageBox.Show("Bạn không thể sửa phiếu đã xác nhận", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                frmPhieuNhapMua frm = new frmPhieuNhapMua();
                frm.MaPhieuNhap = dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString();
                frm.ShowDialog();
                load_dtgPhieuNhap();
            }
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            if (dtgPhieuNhap.RowCount == 0) return;
            string sql = "SELECT [XacNhan] FROM [tbl_PhieuNhapKho] WHERE [SoPhieu] = '" + dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString() + "'";
            bool _XacNhan = bool.Parse(cn.LayDuLieu(sql));
            if (_XacNhan == true)
            {
                MessageBox.Show("Bạn không thể xóa phiếu đã xác nhận.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            else
            {
                DialogResult msg = MessageBox.Show("Bạn chắc chắn muốn xóa?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (msg == DialogResult.Yes)
                {
                    cn.Delete_Table("tbl_ChiTietPhieuNhap", "SoPhieu='" + dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString() + "'");
                    cn.Delete_Table("tbl_LuuKho", "SoPhieu='" + dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString() + "'");
                    cn.Delete_Table("tbl_PhieuNhapKho", "SoPhieu='" + dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString() + "'");
                    MessageBox.Show("Xóa thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            load_dtgPhieuNhap();
        }

        private void ckbMaPhieu_CheckedChanged(object sender, EventArgs e)
        {
            if (ckbMaPhieu.Checked == true)
            {
                txtMaPhieu.Text = "";
                txtMaPhieu.Enabled = true;
            }
            else
            {
                txtMaPhieu.Text = "Tìm theo số phiếu";
                txtMaPhieu.Enabled = false;
            }
        }

        private void ckbKhoNhap_CheckedChanged(object sender, EventArgs e)
        {
            if (ckbKhoNhap.Checked == true)
            {
                cmbKhoNhap.Enabled = true;
            }
            else
            {
                cmbKhoNhap.Enabled = false;
            }
        }

        private void ckbNhaCungCap_CheckedChanged(object sender, EventArgs e)
        {
            if (ckbNhaCungCap.Checked == true)
            {
                cmbNhaCungCap.Enabled = true;
            }
            else
            {
                cmbNhaCungCap.Enabled = false;
            }
        }

        private void ckbNguoiNhan_CheckedChanged(object sender, EventArgs e)
        {
            if (ckbNguoiNhan.Checked == true)
            {
                txtNguoiNhan.Text = "";
                txtNguoiNhan.Enabled = true;
            }
            else
            {
                txtNguoiNhan.Text="Tìm theo người lập phiếu";
                txtNguoiNhan.Enabled = false;
            }
        }

        private void ckbCongTrinhDuAn_CheckedChanged(object sender, EventArgs e)
        {
            if (ckbCongTrinhDuAn.Checked == true)
            {
                cmbCongTrinhDuAn.Enabled = true;
            }
            else
            {
                cmbCongTrinhDuAn.Enabled = false;
            }
        }

        private void ckbNgayNhap_CheckedChanged(object sender, EventArgs e)
        {
            if (ckbNgayNhap.Checked == true)
            {
                dtipTuNgay.Enabled = true;
                dtipDenNgay.Enabled = true;
            }
            else
            {
                dtipTuNgay.Enabled = false;
                dtipDenNgay.Enabled = false;
            }
        }

        private string XacDinhDieuKienLoc()
        {
            string dieuKienLoc = "WHERE 1=1 and tbl_PhieuNhapKho.MaLoaiPhieu='LP001'";
            if (ckbMaPhieu.Checked == true && txtMaPhieu.Text.Trim() != "")
            {
                dieuKienLoc += " AND tbl_PhieuNhapKho.SoPhieu LIKE '%" + txtMaPhieu.Text.Trim() + "%'";
            }

            if (ckbKhoNhap.Checked == true && cmbKhoNhap.Items.Count > 0)
            {
                dieuKienLoc += " AND tbl_PhieuNhapKho.MaKho = '" + cmbKhoNhap.SelectedValue.ToString() + "'";
            }

            if (ckbNhaCungCap.Checked == true && cmbNhaCungCap.Items.Count > 0)
            {
                dieuKienLoc += " AND tbl_PhieuNhapKho.MaDoiTac = '" + cmbNhaCungCap.SelectedValue.ToString() + "'";
            }

            if (ckbNguoiNhan.Checked == true && txtNguoiNhan.Text.Trim() != "")
            {
                dieuKienLoc += " AND tbl_PhieuNhapKho.NguoiNhan like '%" + txtNguoiNhan.Text.Trim() + "%'";
            }
            
            if (ckbCongTrinhDuAn.Checked == true && cmbCongTrinhDuAn.Items.Count > 0)
            {
                dieuKienLoc += " AND dbo.tbl_PhieuNhapKho.MaCongTrinhDuAn = '" + cmbCongTrinhDuAn.SelectedValue.ToString() + "'";
            }

            if(ckbNgayNhap.Checked==true)
            {
                dieuKienLoc += " AND dbo.tbl_PhieuNhapKho.NgayNhap BETWEEN '" + dtipTuNgay.Value.ToString() + "' AND '" + dtipDenNgay.Value.ToString() + "'";
            }

            return dieuKienLoc;
        }

        private void btnTimKiem_Click(object sender, EventArgs e)
        {
            load_dtgPhieuNhap(XacDinhDieuKienLoc());
        }

        private void btnLuu_Click(object sender, EventArgs e)
        {
            frmInPhieuNhapKho frm = new frmInPhieuNhapKho();
            if (dtgPhieuNhap.SelectedRows.Count > 0 && dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value != null)
            {
                string SoPhieu = dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString();
                string sql = "";
                sql = @"SELECT     dbo.tbl_PhieuNhapKho.SoPhieu, dbo.tbl_PhieuNhapKho.NgayNhap, dbo.tbl_DoiTac.TenDoiTac, tbl_DoiTac.DiaChi, dbo.tbl_Kho.TenKho, dbo.tbl_PhieuNhapKho.NguoiNhan, 
                                   dbo.tbl_PhieuNhapKho.DienGiai, dbo.tbl_LoaiPhieu.TenLoaiPhieu, dbo.tbl_CongTrinhDuAn.TenCongTrinhDuAn
                        FROM         dbo.tbl_PhieuNhapKho INNER JOIN
                                              dbo.tbl_DoiTac ON dbo.tbl_PhieuNhapKho.MaDoiTac = dbo.tbl_DoiTac.MaDoiTac INNER JOIN
                                              dbo.tbl_Kho ON dbo.tbl_PhieuNhapKho.MaKho = dbo.tbl_Kho.MaKho INNER JOIN
                                              dbo.tbl_CongTrinhDuAn ON dbo.tbl_PhieuNhapKho.MaCongTrinhDuAn = dbo.tbl_CongTrinhDuAn.MaCongTrinhDuAn INNER JOIN
                                              dbo.tbl_LoaiPhieu ON dbo.tbl_PhieuNhapKho.MaLoaiPhieu = dbo.tbl_LoaiPhieu.MaLoaiPhieu
                        WHERE       tbl_PhieuNhapKho.SoPhieu='" +SoPhieu+"'";
                DataTable tbl = cn.getTable(sql);
                if (tbl.Rows.Count > 0)
                {
                    List<PhieuNhapKho> list = new List<PhieuNhapKho>();
                    //SoPhieu = tbl.Rows[0]["SoPhieu"].ToString();
                    string NgayNhap = tbl.Rows[0]["NgayNhap"].ToString().Substring(0, 10);
                    string DoiTac=tbl.Rows[0]["TenDoiTac"].ToString();
                    string DiaChi=tbl.Rows[0]["DiaChi"].ToString();
                    string TenKho=tbl.Rows[0]["TenKho"].ToString();
                    string NguoiNhan = tbl.Rows[0]["NguoiNhan"].ToString();
                    string DienGiai = tbl.Rows[0]["DienGiai"].ToString();
                    string LoaiPhieu = tbl.Rows[0]["TenLoaiPhieu"].ToString();
                    string CongTrinhDuAn = tbl.Rows[0]["TenCongTrinhDuAn"].ToString();

                    sql = @"SELECT     dbo.tbl_ChiTietPhieuNhap.MaVatTu, dbo.tbl_VatTu.TenVatTu, dbo.tbl_DonViTinh.TenDonViTinh, 
                                       dbo.tbl_ChiTietPhieuNhap.SoLuong, tbl_ChiTietPhieuNhap.DonGia
                            FROM         dbo.tbl_ChiTietPhieuNhap INNER JOIN
                                                  dbo.tbl_DonViTinh ON dbo.tbl_ChiTietPhieuNhap.MaDonViTinh = dbo.tbl_DonViTinh.MaDonViTinh INNER JOIN
                                                  dbo.tbl_VatTu ON dbo.tbl_ChiTietPhieuNhap.MaVatTu = dbo.tbl_VatTu.MaVatTu
                            WHERE     dbo.tbl_ChiTietPhieuNhap.SoPhieu = '"+SoPhieu+"'";
                    DataTable dataChiTiet = cn.getTable(sql);
                    if (dataChiTiet.Rows.Count > 0)
                    {
                        int STT = 0;
                        foreach (DataRow dr in dataChiTiet.Rows)
                        {
                            STT++;
                            PhieuNhapKho phieu = new PhieuNhapKho();
                            phieu.SoPhieu = SoPhieu;
                            phieu.NgayNhap = NgayNhap;
                            phieu.DoiTac = DoiTac;
                            phieu.DiaChi = DiaChi;
                            phieu.TenKho = TenKho;
                            phieu.NguoiNhan = NguoiNhan;
                            phieu.DienGiai = DienGiai;
                            phieu.LoaiPhieu = LoaiPhieu;
                            phieu.CongTrinhDuAn = CongTrinhDuAn;

                            double Tien = float.Parse(dr["SoLuong"].ToString()) * float.Parse(dr["DonGia"].ToString());
                            phieu.STT = STT.ToString();
                            phieu.MaVT=dr["MaVatTu"].ToString();
                            phieu.TenVatTu = dr["TenVatTu"].ToString();
                            phieu.DonViTinh=dr["TenDonViTinh"].ToString();
                            phieu.SoLuong=dr["SoLuong"].ToString();
                            phieu.Gia=dr["DonGia"].ToString();
                            phieu.Tien = Tien.ToString();
                            list.Add(phieu);
                        }
                    }
                     frm.phieuNhapKhoBindingSource.DataSource= list;
                    
                }
            }
            frm.ShowDialog();
        }

        private void radLoad_CheckedChanged(object sender, EventArgs e)
        {
            if (radLoad.Checked == true)
            {
                radLoad.Checked = false;
                load_dtgPhieuNhap();
                ckbNgayNhap.Checked = false;
                ckbMaPhieu.Checked = false;
                ckbKhoNhap.Checked = false;
                ckbNhaCungCap.Checked = false;
                ckbNguoiNhan.Checked = false;
                ckbCongTrinhDuAn.Checked = false;
            }
        }

        private void btnXacNhan_Click(object sender, EventArgs e)
        {
            if (dtgPhieuNhap.SelectedRows.Count > 0 && dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value != null)
            {
               
                    foreach (DataGridViewRow dgvr in dtgChiTietPhieuNhapMua.Rows)
                    {
                        if (dgvr.IsNewRow) continue;
                        string sql = "SELECT MaKho FROM dbo.tbl_PhieuNhapKho WHERE SoPhieu = '" + dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString() + "'";
                        string MaKho = cn.LayDuLieu(sql);
                        string MaVatTu = dgvr.Cells["colMaVatTu"].Value.ToString();
                        string SoPhieu = dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString();
                        string NgayLapPhieu = dtgPhieuNhap.CurrentRow.Cells["colNgayNhap"].Value.ToString();

                        sql = "select SoLuong from tbl_ChiTietPhieuYeuCauNhap where SoPhieu='"+dtgPhieuNhap.CurrentRow.Cells["MaPhieuYeuCau"].Value.ToString()+"' and MaVatTu='"+MaVatTu+"'";
                        DataTable dtSoLuongYeuCau=cn.getTable(sql);
                        float SoLuongYeuCau = float.Parse(dtSoLuongYeuCau.Rows[0]["SoLuong"].ToString());

                        float SoLuongDaNhap = 0;
                        sql = @"SELECT     SUM(dbo.tbl_ChiTietPhieuNhap.SoLuong) AS SoLuongDaNhap
                            FROM         dbo.tbl_ChiTietPhieuNhap INNER JOIN
                                                  dbo.tbl_PhieuNhapKho ON dbo.tbl_ChiTietPhieuNhap.SoPhieu = dbo.tbl_PhieuNhapKho.SoPhieu
                            WHERE     dbo.tbl_PhieuNhapKho.MaPhieuYeuCau = '" + dtgPhieuNhap.CurrentRow.Cells["MaPhieuYeuCau"].Value.ToString() + "' and tbl_PhieuNhapKho.XacNhan=1 AND dbo.tbl_ChiTietPhieuNhap.MaVatTu = '" + MaVatTu + @"'
                            GROUP BY dbo.tbl_ChiTietPhieuNhap.MaVatTu";
                        DataTable dtSoLuong = cn.getTable(sql);
                        if (dtSoLuong.Rows.Count >0) SoLuongDaNhap = float.Parse(dtSoLuong.Rows[0]["SoLuongDaNhap"].ToString());

                        string strSoLuong = "";
                        double SoLuong = 0;
                        if (dgvr.Cells["colSoLuong"].Value.ToString().Contains(','))
                        {
                            strSoLuong = dgvr.Cells["colSoLuong"].Value.ToString().Replace(',', '.');
                        }
                        else
                        {
                            strSoLuong = dgvr.Cells["colSoLuong"].Value.ToString();
                        }
                        double.TryParse(strSoLuong, NumberStyles.AllowDecimalPoint, CultureInfo.InvariantCulture, out SoLuong);

                        if (SoLuong > Math.Round((SoLuongYeuCau - SoLuongDaNhap),2))
                        {
                            MessageBox.Show("Bạn không thể xác nhận phiếu", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                        else
                        {
                            DialogResult msg = MessageBox.Show("Sau khi xác nhận bạn không thể sửa và xóa phiếu!", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                            if (msg == DialogResult.Yes)
                            {
                                cn.Update_Table("tbl_PhieuNhapKho", "XacNhan=1", "SoPhieu='" + dtgPhieuNhap.CurrentRow.Cells["colSoPhieu"].Value.ToString() + "'");
                                cn.Insert_Table("tbl_LuuKho", "SoPhieu,MaKho,MaVatTu,SoLuong,SoLuongPhePham,NgayCapNhat", "'" + SoPhieu + "','" + MaKho + "','" + MaVatTu + "','" + dgvr.Cells["colSoLuong"].Value.ToString() + "','0','" + NgayLapPhieu + "'");
                            }
                    }
                    load_dtgPhieuNhap();
                }
            }
        }
    }
}
